use [Tfs_Warehouse]

SELECT * FROM GetProjectNodeInfoFromReportFolder('/TfsReports/BasicCollection')
SELECT * FROM GetProjectNodeInfoFromReportFolder('/TfsReports/DefaultCollection')
SELECT * FROM GetProjectNodeInfoFromReportFolder('/TfsReports/DefaultCollection/Tailspin Toys')

-- ProjectNodeType = 1 --> Team Project Collection
-- ProjectNodeType = 0 --> Team Project
-- ProjectNodeTypeName gives Node type as 'Team Project Collection' or 'Team Project'

select * from dbo.DimTeamProject
select * from dbo.DimWorkItem
select * from dbo.DimWorkItemCategory
select * from dbo.DimWorkItemLinkType where TeamProjectCollectionSK = 2 order by ReferenceName

select * from dbo.FactWorkItemToCategory order by dbo.FactWorkItemToCategory.WorkItemCategorySK
select * from dbo.FactCurrentWorkItem order by dbo.FactCurrentWorkItem.workitemsk

Declare @ProjectGUID as uniqueidentifier
--SET @ProjectGUID = (SELECT ProjectNodeGUID FROM GetProjectNodeInfoFromReportFolder('/TfsReports/DefaultCollection/Tailspin Toys'))
SET @ProjectGUID = '2EB9C8A2-2243-4897-AC88-602BEF270DD5'
--http://demo1:8080/tfs/DefaultCollection/WorkItemTracking/WorkItem.aspx?artifactMoniker=


SELECT   ToolArtifactDisplayUrl 
	FROM    DimToolArtifactDisplayUrl
	INNER JOIN DimTeamProject tp 
	ON     tp.ParentNodeSK = DimToolArtifactDisplayUrl.TeamProjectCollectionSK
	WHERE   ToolType in ('WorkItemTracking/Workitem','VersionControl/Changeset')
	AND    tp.ProjectNodeGUID = @ProjectGUID
		
	
DECLARE @TfsWorkItemTrackingDisplayUrl as nvarchar(128)

DECLARE @workItemUrlIndex INT

SET @TfsWorkItemTrackingDisplayUrl = (
SELECT   ToolArtifactDisplayUrl 
	FROM    DimToolArtifactDisplayUrl
	INNER JOIN DimTeamProject tp 
	ON     tp.ParentNodeSK = DimToolArtifactDisplayUrl.TeamProjectCollectionSK
	WHERE   ToolType = 'WorkItemTracking/Workitem' or ToolType = 'VersionControl/Changeset'
	AND    tp.ProjectNodeGUID = @ProjectGUID
	)
	
SELECT 	@TfsWorkItemTrackingDisplayUrl
	
--SET @workItemUrlIndex = (SELECT PATINDEX('%/WorkItemTracking/WorkItem.aspx?artifactMoniker=%',@TfsWorkItemTrackingDisplayUrl))
--SET @workItemUrlIndex = (SELECT PATINDEX('%tfs%',@TfsWorkItemTrackingDisplayUrl))

--SELECT	@workItemUrlIndex

SELECT SUBSTRING(@TfsWorkItemTrackingDisplayUrl, 1, (SELECT PATINDEX('%tfs%',@TfsWorkItemTrackingDisplayUrl))-1) + 'tfs'





	(
		SELECT   ToolArtifactDisplayUrl 
		FROM    DimToolArtifactDisplayUrl
		INNER JOIN DimTeamProject tp 
		ON     tp.ParentNodeSK = DimToolArtifactDisplayUrl.TeamProjectCollectionSK
		WHERE   ToolType = 'WorkItemTracking/Workitem'
		AND    tp.ProjectNodeGUID = @ProjectGUID
	)






	
SELECT PATINDEX('%/WorkItemTracking/WorkItem.aspx?artifactMoniker=%',ToolArtifactDisplayUrl)
 FROM
(
	SELECT   ToolArtifactDisplayUrl 
	FROM    DimToolArtifactDisplayUrl
	INNER JOIN DimTeamProject tp 
	ON     tp.ParentNodeSK = DimToolArtifactDisplayUrl.TeamProjectCollectionSK
	WHERE   ToolType = 'WorkItemTracking/Workitem'
	AND    tp.ProjectNodeGUID = @ProjectGUID
) A

select System_Id as ReqID, System_Title as ReqTitle, 
	(
		select '*' + cast(System_Id as nvarchar(8))  +  '-' + cast(System_Id as nvarchar(8)) + ', ' 
		from CurrentWorkItemView CWV2
		join vFactLinkedCurrentWorkItem FLCW2 on FLCW2.TargetWorkitemSK = CWV2.WorkItemSK
		where SourceWorkItemSK = FLCW.SourceWorkItemSK 
		and System_WorkItemType = 'Task'
		for XML PATH('') 
	 ) as TaskID,
	 (
		select '*' + cast(System_Id as nvarchar(8))  +  '-' + cast(System_Id as nvarchar(8)) + ', ' 
		from CurrentWorkItemView CWV2
		join vFactLinkedCurrentWorkItem FLCW2 on FLCW2.TargetWorkitemSK = CWV2.WorkItemSK
		where SourceWorkItemSK = FLCW.SourceWorkItemSK 
		and System_WorkItemType = 'Change Request'
		for XML PATH('') 
	 ) as ChangeRequestID,
	 (
		select '*' + cast(System_Id as nvarchar(8))  +  '-' + cast(System_Id as nvarchar(8)) + ', ' 
		from CurrentWorkItemView CWV2
		join vFactLinkedCurrentWorkItem FLCW2 on FLCW2.TargetWorkitemSK = CWV2.WorkItemSK
		where SourceWorkItemSK = FLCW.SourceWorkItemSK 
		and System_WorkItemType = 'Test Case'
		for XML PATH('') 
	 ) as TestID,
	 (
	   select distinct '*' + cast(System_Id as nvarchar(8))  +  '-' + cast(System_Id as nvarchar(8)) + ', '
	   from vFactLinkedCurrentWorkItem FLCW2 
	   join vFactLinkedCurrentWorkItem FLCW3 on FLCW3.SourceWorkitemSK = FLCW2.TargetWorkitemSK
	   join CurrentWorkItemView CWV2 on CWV2.WorkItemSK = FLCW3.TargetWorkItemSK
	   where FLCW2.SourceWorkItemSK = FLCW.SourceWorkItemSK 
	   and System_WorkItemType = 'Bug'
	   for XML PATH('') 
	 ) as BugID,
	 (
		select '*' + cast(System_Id as nvarchar(8))  +  '-' + cast(System_Id as nvarchar(8)) + ', ' 
		from CurrentWorkItemView CWV2
		join vFactLinkedCurrentWorkItem FLCW2 on FLCW2.TargetWorkitemSK = CWV2.WorkItemSK
		where SourceWorkItemSK = FLCW.SourceWorkItemSK 
		and System_WorkItemType = 'Bug'
		for XML PATH('') 
	 ) as BugID2
from CurrentWorkItemView CWV
left join vFactLinkedCurrentWorkItem FLCW on FLCW.SourceWorkItemSK = CWV.WorkItemSK
where 
CWV.ProjectNodeGUID = @ProjectGUID and 
System_WorkItemType in ('Requirement','User Story')
Group by FLCW.SourceWorkItemSK, System_Id, System_Title
order by System_Id asc

--TODO
--Generize for all work items that might be added in the future to the Req. Category

--Declare @ProjectGUID as uniqueidentifier
--SET @ProjectGUID = (SELECT ProjectNodeGUID FROM GetProjectNodeInfoFromReportFolder('/TfsReports/DefaultCollection/RTM'))
--SET @ProjectGUID = '8FDC8BBF-92C4-4151-8E51-8F313EBABC64'

-- [dbo].[GetWorkItemsForLinkPath]
--	@projectCollectionGUID - Team Project Collection GUID
--  @workItemIdList - comma separated list of workitems -  best to start at root elements
--	@linkPath - comma separated list of link type names e.g. Parent, Child, Tested By, etc.
--	@asOfDate - DateTime point you want the information from going back
--  @maxHops - How far down in the heirarchy do you want to traverse links

-- Requirement Work Item Type
SELECT * FROM [dbo].[GetWorkItemsForLinkPath](CONVERT(uniqueidentifier, '7E83F72E-AC85-4849-9E26-AC9B9A95A27A'), '45, 52, 55', 'Child, Tested By', SYSDATETIME(), 3)

-- Use Case Work Item Type
SELECT * FROM [dbo].[GetWorkItemsForLinkPath](CONVERT(uniqueidentifier, '7E83F72E-AC85-4849-9E26-AC9B9A95A27A'), '46', 'Child, Tested By', SYSDATETIME(), 3)

-- Task Work Item Type
SELECT * FROM [dbo].[GetWorkItemsForLinkPath](CONVERT(uniqueidentifier, '7E83F72E-AC85-4849-9E26-AC9B9A95A27A'), '47', 'Parent', SYSDATETIME(), 3)

-- Test Case Item Type
SELECT * FROM [dbo].[GetWorkItemsForLinkPath](CONVERT(uniqueidentifier, '7E83F72E-AC85-4849-9E26-AC9B9A95A27A'), '67', 'Tests', SYSDATETIME(), 3)


SELECT * FROM [dbo].[GetWorkItemsForLinkPath](CONVERT(uniqueidentifier, '7E83F72E-AC85-4849-9E26-AC9B9A95A27A'), '47', 'Child', SYSDATETIME(), 3)
SELECT * FROM [dbo].[GetWorkItemsForLinkPath](CONVERT(uniqueidentifier, '7E83F72E-AC85-4849-9E26-AC9B9A95A27A'), '49', 'Child', SYSDATETIME(), 3)


--ORDER BY RootWorkItemID, LinkedWorkItemID
UNION
SELECT * FROM
(SELECT * FROM [dbo].[GetWorkItemsForLinkPath](CONVERT(uniqueidentifier, '7E83F72E-AC85-4849-9E26-AC9B9A95A27A'), '45', 'Tested By', SYSDATETIME(), 3)) a
WHERE a.LinkTypeSK IS NOT NULL
--ORDER BY RootWorkItemID, LinkedWorkItemID

SELECT * FROM [dbo].[GetWorkItemsTree](CONVERT(uniqueidentifier, '7E83F72E-AC85-4849-9E26-AC9B9A95A27A'), '45', 'Child', SYSDATETIME())
SELECT * FROM [dbo].[GetWorkItemsTree](CONVERT(uniqueidentifier, '7E83F72E-AC85-4849-9E26-AC9B9A95A27A'), '46', 'Tested By', SYSDATETIME())

--SELECT * FROM [dbo].[GetWorkItemsForLinkPathXml](CONVERT(uniqueidentifier, '7E83F72E-AC85-4849-9E26-AC9B9A95A27A'), '7', 'Child', SYSDATETIME(), 3)
--SELECT * FROM [dbo].[GetWorkItemsTreeXml](CONVERT(uniqueidentifier, '7E83F72E-AC85-4849-9E26-AC9B9A95A27A'), '7', 'Child', SYSDATETIME(), 3)


